﻿using System;

using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using PublicTransport.DAL.DTO;
using System.Globalization;
using PublicTransport.Helpers;

namespace PublicTransport.DAL
{
    /// <summary>   Rozhranie s DB, ktoré sprístupňuje údaje o staniciach </summary>
    class StationDao : IStationDao
    {

        /// <summary> Pripojenie k databáze, nezatvára sa </summary>
        SQLiteConnection connection;

        /// <summary>   Počas vytvárania sa získa spojenie s DB. </summary>
        public StationDao()
        {
            this.connection = DBHelper.GetConnection();
        }

        /// <summary>   Odstráni diakritiku </summary>
        ///
        /// <param name="s">    String, z ktorého sa má odstrániť diakritika</param>
        ///
        /// <returns> String, ktorý má odstránenú diakritiku </returns>
        public static string RemoveDiacritics(string s)
        {
            //s = s.Normalize(NormalizationForm.FormD);
            StringBuilder sb = new StringBuilder();

            for (int i = 0; i < s.Length; i++)
            {
                if (CharUnicodeInfo.GetUnicodeCategory(s[i]) != UnicodeCategory.NonSpacingMark) sb.Append(s[i]);
            }

            return sb.ToString();
        }

        /// <summary>   Vráti zastávku podľa presného názvu. Hľadá bez diakritiky </summary>
        ///
        /// <exception cref="ArgumentException">   V prípade, že sa našla viac ako jedna
        /// 									   zastávka s daným názvom </exception>
        ///
        /// <param name="name"> Presný názov stanice - BEZ DIAKRITIKY. </param>
        ///
        /// <returns>   Nájdená stanica. </returns>
        public StationDTO GetStation(string name)
        {
            SQLiteCommand selectCmnd = new SQLiteCommand(connection);
            selectCmnd.CommandText = "SELECT * FROM station WHERE normalized_name LIKE @nameValue";
            selectCmnd.Parameters.AddWithValue("nameValue", Helper.RemoveAccents(name));

            List<StationDTO> stations = GetStations(selectCmnd);
            if (stations.Count == 1)
            {
                return stations[0];
            }
            else
                if (stations.Count == 0)
                {
                    return null;
                }
                else
                {
                    throw new ArgumentException("Search string found more than one station");
                }
        }

        /// <summary>   Vráti zastávku ID kľúča. </summary>
        ///
        /// <exception cref="ArgumentException">   Bolo nájdených viac zastávok </exception>
        ///
        /// <param name="id">   ID primárny kľúč stanice </param>
        ///
        /// <returns>   Nájdená stanica. </returns>

        public StationDTO GetStation(int id)
        {
            SQLiteCommand selectCmnd = new SQLiteCommand(connection);
            selectCmnd.CommandText = "SELECT * FROM station WHERE id=@idValue";
            selectCmnd.Parameters.AddWithValue("idValue", id);

            List<StationDTO> stations = GetStations(selectCmnd);
            if (stations.Count == 1)
            {
                return stations[0];
            }
            else
                if (stations.Count == 0)
                {
                    throw new ArgumentException("The station with this ID was not found");
                }
                else
                {
                    throw new ArgumentException("The ID found more record - this should not happen");
                }

        }

        /// <summary>   Vráti všetky existujúce zastávky. </summary>
        ///
        /// <returns>   Zoznam zastávok. </returns>
        public List<StationDTO> GetAllStations()
        {
            SQLiteCommand selectCmnd = new SQLiteCommand(connection);
            selectCmnd.CommandText = "SELECT * FROM station";
            return GetStations(selectCmnd);
        }

        /// <summary>   Vráti všetky názvy zastávok (nie celé objekty) </summary>
        ///
        /// <returns>   Zoznam názvov zastávok. </returns>
        public List<String> GetAllStationNames()
        {
            SQLiteCommand selectCmnd = new SQLiteCommand(connection);
            selectCmnd.CommandText = "SELECT * FROM station";

            using (selectCmnd)
            {
                using (SQLiteDataReader reader = selectCmnd.ExecuteReader())
                {
                    List<String> stationList = new List<String>();
                    while (reader.Read())
                    {
                        stationList.Add(reader[DBHelper.STATION_NAME].ToString());
                    }
                    return stationList;
                }

            }
        }

        /// <summary>   Vráti zoznam dosiahnuteľných zastávok z danej zastávky v danom čase. </summary>
        ///
        /// <param name="startId">  Zastávka, z ktorej hľadáme dosiahnuteľné spoje. </param>
        /// <param name="time">     Čas, v ktorom chceme spoje nájsť. </param>
        ///
        /// <returns>   Dosiahnuteľné stanice </returns>
        public List<ReacheableStationDTO> GetReacheableStations(int startId, DateTime time)
        {
            SQLiteCommand selectCmnd = new SQLiteCommand(connection);
            /*
            selectCmnd.CommandText = "SELECT  f.line_id,g.destination_station_id,g.distance  FROM ( " +
                                     "SELECT min(time) as mintime, time_table.line_id,reacheable_table.destination_station_id as destination_station_id FROM time_table INNER JOIN reacheable_table ON time_table.line_id=reacheable_table.line_id and start_station_id=@startId WHERE time_table.station_id=@startId and day_type='n' AND time>@fromTime AND time<@toTime AND time_table.line_id IN (SELECT line_id FROM reacheable_table WHERE start_station_id=@startId) group by destination_station_id ) " +
                                     "AS x INNER JOIN time_table as f, reacheable_table as g on f.line_id = x.line_id AND f.time=x.mintime and f.day_type='n' AND g.start_station_id=@startId AND x.destination_station_id = g.destination_station_id and x.line_id=g.line_id;";
            */
            selectCmnd.CommandText = "SELECT time_table.line_id, time, distance, station_id, reacheable_table.destination_station_id FROM time_table INNER JOIN reacheable_table ON time_table.line_id=reacheable_table.line_id AND time_table.station_id=@startId WHERE reacheable_table.start_station_id=@startId AND day_type='n' AND time>@fromTime AND time<@toTime AND time_table.line_id IN (SELECT line_id FROM reacheable_table WHERE start_station_id=@startId);";
            selectCmnd.Parameters.AddWithValue("startId", startId);
            selectCmnd.Parameters.AddWithValue("fromTime", (time.Hour * 100) + time.Minute);
            selectCmnd.Parameters.AddWithValue("toTime", (time.Hour * 100) + time.Minute + 20);


            Dictionary<int, ReacheableStationDTO> stationTransit = new Dictionary<int, ReacheableStationDTO>();
            foreach (ReacheableStationDTO station in GetReacheableStations(selectCmnd))
            {
                if (stationTransit.ContainsKey(station.StationId))
                {
                    if ((stationTransit[station.StationId]).Time < station.Time)
                    {
                        stationTransit[station.StationId] = station;
                    }
                }
                else
                {
                    stationTransit.Add(station.StationId, station);
                }
            }


            return new List<ReacheableStationDTO>(stationTransit.Values);
       }

        /// <summary>   Vráti zoznam dosiahnuteľných zastávok z danej zastávky v danom čase. </summary>
        ///
        /// <param name="cmnd"> SQLiteCommand, ktorý vyberie stanice </param>
        ///
        /// <returns>   Dosiahnuteľné stanice. </returns>

        private List<ReacheableStationDTO> GetReacheableStations(SQLiteCommand cmnd)
        {
            using (cmnd)
            {
                using (SQLiteDataReader reader = cmnd.ExecuteReader())
                {
                    List<ReacheableStationDTO> stationList = new List<ReacheableStationDTO>();
                    while (reader.Read())
                    {
                        ReacheableStationDTO station = new ReacheableStationDTO();
                        //station.TimeTableId = int.Parse(reader["id"].ToString());
                        station.StationId = int.Parse(reader["destination_station_id"].ToString());
                        station.Distance = int.Parse(reader["distance"].ToString());
                        station.LineId = int.Parse(reader["line_id"].ToString());
                        station.Time = int.Parse(reader["time"].ToString());
                        stationList.Add(station);
                    }
                    return stationList;
                }

            }
        }

        /// <summary>   Získa TimeTable podľa zadaného SQL query </summary>
        ///
        /// <param name="cmnd"> SQLiteCommand, ktorý vyberie stanice. </param>
        ///
        /// <returns>    </returns>
        private List<TimeTableDTO> GetLineTimes(SQLiteCommand cmnd)
        {
            using (cmnd)
            {
                using (SQLiteDataReader reader = cmnd.ExecuteReader())
                {
                    List<TimeTableDTO> timeTableList = new List<TimeTableDTO>();
                    while (reader.Read())
                    {
                        TimeTableDTO timeTable = new TimeTableDTO();
                        timeTable.StationId = int.Parse(reader["station_id"].ToString());
                        timeTable.LineId = int.Parse(reader["line_id"].ToString());
                        timeTable.Time = int.Parse(reader["time"].ToString());
                        //timeTable.DayType =reader["day_type"].ToString();
                        timeTableList.Add(timeTable);
                    }
                    return timeTableList;
                }

            }
        }

        /// <summary>  Vráti zoznam spojov (ich kľúče) podľa query </summary>
        ///
        /// <param name="cmnd"> SQLiteCommand, ktorý vyberie stanice. </param>
        ///
        /// <returns>   Zoznam identifikačných kľúčov staníc </returns>
        private List<int> GetLines(SQLiteCommand cmnd)
        {
            using (cmnd)
            {
                using (SQLiteDataReader reader = cmnd.ExecuteReader())
                {
                    List<int> lineList = new List<int>();
                    while (reader.Read())
                    {
                        lineList.Add(int.Parse(reader["line_id"].ToString()));
                    }
                    return lineList;
                }

            }
        }

        /// <summary>   Získa objekty reacheable table podľa uvedenej query. </summary>
        ///
        /// <param name="cmnd"> SQLiteCommand, ktorý vyberie stanice. </param>
        ///
        /// <returns>   Objekty reacheable_table získané z DB </returns>

        private List<ReacheableTableDTO> GetReacheableTable(SQLiteCommand cmnd)
        {
            using (cmnd)
            {
                using (SQLiteDataReader reader = cmnd.ExecuteReader())
                {
                    List<ReacheableTableDTO> tableList = new List<ReacheableTableDTO>();
                    while (reader.Read())
                    {
                        ReacheableTableDTO table = new ReacheableTableDTO();
                        table.LineId = int.Parse(reader["line_id"].ToString());
                        //table.StartStationId= int.Parse(reader["start_station_id"].ToString());
                        table.DestinationStationId = int.Parse(reader["destination_station_id"].ToString());
                        table.Distance = int.Parse(reader["distance"].ToString());
                        tableList.Add(table);
                    }
                    return tableList;
                }

            }
        }

        /// <summary>  Vráti zoznam objektov staníc z DB podľa query </summary>
        ///
        /// <param name="cmnd"> SQLiteCommand, ktorý vyberie stanice. </param>
        ///
        /// <returns>   Zoznam staníc, ktoré sú získane z danej query </returns>

        private List<StationDTO> GetStations(SQLiteCommand cmnd)
        {
            using (cmnd)
            {
                using (SQLiteDataReader reader = cmnd.ExecuteReader())
                {
                    List<StationDTO> stationList = new List<StationDTO>();
                    while (reader.Read())
                    {
                        StationDTO station = new StationDTO();
                        station.Name = reader[DBHelper.STATION_NAME].ToString();
                        station.Id = int.Parse(reader[DBHelper.STATION_ID].ToString());
                        String latitudeString = reader["latitude"].ToString();
                        if (latitudeString.Length > 0)
                        {
                            station.Latitude = double.Parse(latitudeString);
                        }
                        else
                        {
                            station.Latitude = null;
                        }
                        String longtitudeString = reader["longtitude"].ToString();
                        if (longtitudeString.Length > 0)
                        {
                            station.Longtitude = double.Parse(longtitudeString);
                        }
                        else
                        {
                            station.Longtitude = null;
                        }
                        stationList.Add(station);
                    }
                    return stationList;
                }

            }
        }
    }
}
